package no.sogl.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelHelper {
    public static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CreationHelper createHelper = wb.getCreationHelper();
        // Header
        CellStyle style = wb.createCellStyle();
        // style.setBorderRight(CellStyle.BORDER_THIN);
        // style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // style.setBorderBottom(CellStyle.BORDER_THIN);
        // style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // style.setBorderLeft(CellStyle.BORDER_THIN);
        // style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // style.setBorderTop(CellStyle.BORDER_THIN);
        // style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        // style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        // style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        Font headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 12);
        style.setFont(headerFont);
        styles.put("header", style);

        // Left
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        style.setWrapText(true);
        styles.put("left", style);

        // Center
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        style.setWrapText(true);
        styles.put("center", style);

        // Right
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        styles.put("right", style);

        style = wb.createCellStyle();
        style.setDataFormat(createHelper.createDataFormat().getFormat("d.m.yyyy"));
        styles.put("date", style);
        
        return styles;
    }

    public static void createCell(Row row, int col, Object value, CellStyle style) {
        Cell cell = row.createCell(col);
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Short) {
            cell.setCellValue((Short) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue((Integer) value);
        }
        if (style != null) cell.setCellStyle(style);
    }

    public static String getCellString(Cell cell, boolean trim) {
        String s = getCellString(cell);
        if (trim) return s.trim();
        return s;
    }

    public static String getCellString(Cell cell) {
        Object o = getCellValue(cell);
        if (o instanceof String) {
            return (String) o;
        } else if (o instanceof Double) {
            Double d = (Double) o;
            if (new Double(d.longValue()).equals(d)) {
                return Long.toString(d.longValue());
            }
        }
        return "";
    }

    public static Integer getCellInteger(Cell cell) {
        Object o = getCellValue(cell);
        if (o == null) {
            return null;
        }
        if (o instanceof Double) {
            Double d = (Double) o;

            if (new Double(d.intValue()).equals(d)) {
                return d.intValue();
            }
        } else if (o instanceof String) {
            try {
                return Integer.valueOf((String) o).intValue();
            } catch (NumberFormatException ne) {
                throw new RuntimeException("Invalid numerical data " + o, ne);
            }
        }

        throw new RuntimeException("Invalid numerical data " + o);
    }

    public static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case HSSFCell.CELL_TYPE_NUMERIC:
                return new Double(cell.getNumericCellValue());
            case HSSFCell.CELL_TYPE_BLANK:
                return null;
            case HSSFCell.CELL_TYPE_FORMULA:
                String s = cell.getStringCellValue();
                if ("".equals(s)) {
                    return new Double(cell.getNumericCellValue());
                } else {
                    return s;
                }
            default:
                throw new RuntimeException("Unknown cell type in row :" + cell.getRowIndex() + 1 + ", column: "
                        + cell.getColumnIndex() + 1);
        }
    }

    public static Date getCellDate(Cell cell, String dateFormat) {
        Object o = getCellValue(cell);
        if (o == null) {
            return null;
        }
        if (o instanceof String) {
            return parseDateString((String) o, dateFormat);
        } else if (o instanceof Double && DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }

        throw new RuntimeException("Invalid dateformat " + o + " (" + dateFormat + ")");
    }

    public static Date parseDateString(String s, String dateFormat) {
        SimpleDateFormat df = new SimpleDateFormat(dateFormat);
        Date date = null;

        try {
            date = df.parse(s);
        } catch (ParseException pe) {
            throw new RuntimeException("Invalid dateformat " + s + " (" + dateFormat + ")");
        }
        return date;
    }
}
